package org.ftz;
import org.ftz.control.DBUtil;
import org.ftz.pojo.Problem;
import org.ftz.pojo.Status;
import org.ftz.pojo.StuShow;
import org.junit.Test;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class test6 {
    public List<Problem> selectLimit(int a,int b){  //客户端展示题目
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Problem> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "select id,title,level from oj_problem limit ?,?";  // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,a);
            statement.setInt(2,b);
            resultSet = statement.executeQuery(); // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                Problem problem = new Problem();
                problem.setId(resultSet.getInt("id"));
                problem.setTitle(resultSet.getString("title"));
                problem.setLevel(resultSet.getString("level"));
                list.add(problem);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return list;
    }
    public List<Integer> selectCount(int a,int b){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Integer> count = new ArrayList<>();
        List<Problem> problems = selectLimit(a, b);
        try {
            connection = DBUtil.getConnection();
            String sql2="select count(*) from oj_status WHERE id=?";
            for(int i=0;i<problems.size();i++){
                statement = connection.prepareStatement(sql2);
                statement.setInt(1,problems.get(i).getId());
                resultSet=statement.executeQuery();
                if(resultSet.next()){
                    count.add(resultSet.getInt(1));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return count;
    }
    public List<Integer> selectaccnt(int a,int b){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Integer> count = new ArrayList<>();
        List<Problem> problems = selectLimit(a, b);
        try {
            connection = DBUtil.getConnection();
            String sql2="select count(*) from oj_status WHERE id=? and status='Accepted'";
            for(int i=0;i<problems.size();i++){
                statement = connection.prepareStatement(sql2);
                statement.setInt(1,problems.get(i).getId());
                resultSet=statement.executeQuery();
                if(resultSet.next()){
                    count.add(resultSet.getInt(1));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return count;
    }
    public List<StuShow> clientshow(int a,int b){
        List<StuShow> stuShows=new ArrayList<>();
        DecimalFormat decimalFormat=new DecimalFormat("#.##%");
        List<Problem> problems = selectLimit(a, b);
        List<Integer> count = selectCount(a, b);
        List<Integer> selectaccnt = selectaccnt(a, b);
        for(int i=0;i<problems.size();i++){
            StuShow stuShow = new StuShow();
            stuShow.setProblem(problems.get(i));
            stuShow.setCount(count.get(i));
            stuShow.setAccount(selectaccnt.get(i));
            if(stuShow.getCount()==0){
                stuShow.setAcrate("0%");
            }else {
                double x=(stuShow.getAccount()*1.0)/stuShow.getCount();
                String s = decimalFormat.format(x);
                stuShow.setAcrate(s);
            }
            stuShows.add(stuShow);
        }
        return stuShows;
    }
    @Test
    public void test01(){
        List<StuShow> stuShows = clientshow(0, 2);
        for(StuShow stuShow:stuShows){
            System.out.println(stuShow);
        }
    }
    @Test
    public void test02() throws UnknownHostException {
        Connection connection = null;
        PreparedStatement statement =null;
        String ip= InetAddress.getLocalHost().getHostAddress();
        Status status = new Status(ip,1000,"Accepted","C++",new Date());
        try {
            connection = DBUtil.getConnection(); // 1. 建立连接
            String sql = "insert into oj_status values(?,?,?,?,?)";  // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setString(1,status.getIp());
            statement.setInt(2,status.getId());
            statement.setString(3,status.getStatus());
            statement.setString(4,status.getLanguage());
            long time=status.getTime().getTime();
            statement.setTimestamp(5,new java.sql.Timestamp(time) );
            int ret = statement.executeUpdate();  // 3. 执行 SQL 语句
            if(ret == 1){
                System.out.println("插入成功!");
            }else {
                System.out.println("插入失败!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
    }
    public List<Status> selectSta(int a,int b){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Status> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "select ip,id,status,language,time from oj_status ORDER BY time DESC limit ?,? "; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setInt(1,a);
            statement.setInt(2,b);
            resultSet = statement.executeQuery();  // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                Status status = new Status();
                status.setIp(resultSet.getString(1));
                status.setId(resultSet.getInt(2));
                status.setStatus(resultSet.getString(3));
                status.setLanguage(resultSet.getString(4));
                Timestamp timestamp = resultSet.getTimestamp(5);
                String s = String.valueOf(timestamp);
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date date=simpleDateFormat.parse(s.split("\\.")[0]);
                status.setTime(date);
                list.add(status);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return list;
    }
    @Test
    public void test03(){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Status> statuses = selectSta(0, 9);
        for(Status status:statuses){
            System.out.println(simpleDateFormat.format(status.getTime()));
        }
    }
    public List<Status> selectStaByIp(int a, int b){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Status> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();  // 1. 建立连接
            String sql = "select ip,id,status,language,time from oj_status where ip=? ORDER BY time DESC limit ?,? "; // 2. 拼装 SQL 语句
            statement = connection.prepareStatement(sql);
            statement.setString(1,InetAddress.getLocalHost().getHostAddress());
            statement.setInt(2,a);
            statement.setInt(3,b);
            resultSet = statement.executeQuery(); // 3. 执行 SQL 语句
            while (resultSet.next()){ // 4. 遍历结果集
                Status status = new Status();
                status.setIp(resultSet.getString(1));
                status.setId(resultSet.getInt(2));
                status.setStatus(resultSet.getString(3));
                status.setLanguage(resultSet.getString(4));
                Timestamp timestamp = resultSet.getTimestamp(5);
                String s = String.valueOf(timestamp);
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date date=simpleDateFormat.parse(s.split("\\.")[0]);
                status.setTime(date);
                list.add(status);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet); // 5. 关闭释放资源
        }
        return list;
    }
    @Test
    public void test04() throws UnknownHostException {
        System.out.println(InetAddress.getLocalHost().getHostAddress());
        List<Status> statuses = selectStaByIp(0, 2);
        for(Status status:statuses){
            System.out.println(status);
        }
    }
}
